import glob
import xlrd
from lxml import etree
import pymysql


def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook('template.xls')
    # 获取所有sheet
    print(workbook.sheet_names())
    # 获取sheet2
    sheet2_name = workbook.sheet_names()[0]
    print(sheet2_name)
    # 根据sheet索引或者名称获取sheet内容
    sheet2 = workbook.sheet_by_name(sheet2_name)

    print("nrows=%d,ncols=%d" % (sheet2.nrows, sheet2.ncols))

    result = {}

    index = 1
    while index < sheet2.nrows:
        rows = sheet2.row_values(index)
        key = rows[1] + "-" + str(rows[2])
        result[key] = {"id": rows[0], "name": rows[1], "build": rows[2], "sn": rows[3]}
        index = index + 1

    return result


def insert_to_data(id, name, build, sn, collector, function, functions_desc):
    fun_desc = 1

    if len(functions_desc) == 0:
        fun_desc = 1
    else:
        fun_desc = int(functions_desc)
        if fun_desc == 0:
            fun_desc = 1

    print("name=" + name + ", fun_desc=" + str(fun_desc))

    # 创建连接
    conn = pymysql.connect(host='rm-uf63r801033wg0e41lo.mysql.rds.aliyuncs.com', port=3306, user='boslend_v4',
                           passwd='iKeO2fVgMDi9JCkh', db="boslend_v4")
    # 创建游标
    cursor = conn.cursor()

    # 执行sql，并返回受影响的行数
    cursor.execute("select * from dev_device_instance where id = %s", sn)
    item = cursor.fetchall()

    if len(item) > 0:
        # print(item[0])
        # try:
        # 执行SQL语句
        cursor.execute(
            "INSERT INTO test_nb_data(dev_id,dev_name,dev_build,collector,function,sn,blt_name,blt_build,multiple) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);",
            (id, name, build, collector, function, sn, item[0][2], item[0][7], fun_desc))
        # 提交到数据库执行
        conn.commit()

    # 关闭数据库连接
    conn.close()


if __name__ == '__main__':
    excel_result = read_excel()
    # print(excel_result)

    xmlfiles = glob.glob('330200/*/*.xml')
    size = 0

    for file in xmlfiles:
        # print(file)
        html = etree.parse(file, etree.HTMLParser(encoding="gb2312"))  # 指定解析器HTMLParser会根据文件修复HTML文件中缺失的如声明信息
        result = etree.tostring(html)  # 解析成字节
        collectors = html.xpath('//collector[1]/attribute::*')  # 获取所有子孙节点的li节点
        # print(collectors)
        meters = html.xpath('//meter/@desc')  # 获取所有子孙节点的li节点
        # print(meters)

        collector_name = collectors[0]
        if ':' in collectors[0]:
            collector_name = collectors[0].split(":")[0]

        # print(collector_name)

        meter_len = len(meters)
        index = 1
        while index <= meter_len:

            str_id = '//meter[%d]/function/@id' % (index)
            functions_id = html.xpath(str_id)
            # print(functions_id)

            str_name = '//meter[%d]/function/@name' % (index)
            functions_name = html.xpath(str_name)

            str_desc = '//meter[%d]/function/@desc' % (index)
            functions_desc = html.xpath(str_desc)

            if len(functions_name) > 0:

                key = meters[index - 1] + "-" + collector_name
                # key = functions_name[0] + "-" + collector_name

                if key in excel_result:
                    sn = excel_result.get(key).get('sn')
                    if len(sn) != 0:
                        insert_to_data(str(excel_result.get(key).get('id')), str(excel_result.get(key).get('name')),
                                       str(excel_result.get(key).get('build')), str(excel_result.get(key).get('sn')),
                                       collectors[2], functions_id[0], functions_desc[0])
                        size = size + 1
                    excel_result.pop(key)
                else:
                    key = functions_name[0] + "-" + collector_name
                    if key in excel_result:
                        sn = excel_result.get(key).get('sn')

                        if len(sn) != 0:
                            insert_to_data(str(excel_result.get(key).get('id')), str(excel_result.get(key).get('name')),
                                           str(excel_result.get(key).get('build')),
                                           str(excel_result.get(key).get('sn')), collectors[2], functions_id[0],
                                           functions_desc[0])
                            size = size + 1
                        excel_result.pop(key)
                    # else:
                    #     print(
                    #         "collector_name:" + collector_name + ",meter:" + meters[index - 1] + ",id:" + functions_id[
                    #             0] + ", name:" +
                    #         functions_name[0])
            index = index + 1

    onsize = 0
    for key in excel_result.keys():
        sn = excel_result.get(key).get('sn')
        if len(sn) != 0:
            sn = str(excel_result.get(key).get('sn'))
            print(excel_result.get(key))
            onsize = onsize + 1

    print(size)
    print(onsize)
